home *** CD-ROM | disk | FTP | other *** search
- Rem
- Rem odbccat8.sql
- Rem
- Rem $Id: odbccat8.sql,v 1.3 1998/08/28 09:39:15 source Exp $
- Rem
- Rem OpenLink ODBC Catalog views for Oracle 8 RDBMS
- Rem
- Rem These catalog views provide the ODBC catalog for the
- Rem SQLForeignKeys and SQLPrimaryKeys functions.
- Rem If you plan to use these functions from ODBC, please install
- Rem these views. Refer to your OpenLink database agent documentation
- Rem for further details.
- Rem
- Rem (C)Copyright 1997 OpenLink Software.
- Rem All Rights Reserved.
- Rem
- Rem The copyright above and this notice must be preserved in all
- Rem copies of this source code. The copyright above does not
- Rem evidence any actual or intended publication of this source code.
- Rem
- Rem This is unpublished proprietary trade secret source code of
- Rem OpenLink Software. This source code may not be copied, disclosed,
- Rem distributed, demonstrated or licensed except as expressly
- Rem authorized by OpenLink Software.
- Rem
- Rem
- Rem NOTICE:
- Rem
- Rem This script must be run connected to the internal database
- Rem in order to register properly. As this might require the DBA
- Rem to enter a password this script needs to be run as follows:
- Rem
- Rem log in as the Oracle DBA
- Rem $ svrmgrl
- Rem SQL> connect internal
- Rem enter password when requested by Oracle
- Rem SQL> @odbccat8.sql
- Rem ...........
- Rem <output from running Oracle script deleted>
- Rem ...........
- Rem SQL> quit
- Rem
- Rem
- /
- create or replace view OPENLINK$SQL_FOREIGN_KEYS
- (PKTABLE_QUALIFIER, PKTABLE_OWNER, PKTABLE_NAME, PKCOLUMN_NAME,
- FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, FKCOLUMN_NAME,
- KEY_SEQ, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME)
- as
- select NULL, po.name, pb.name, pcl.name,
- NULL, fo.name, fb.name, fcl.name,
- fcc.pos#,
- 1,
- decode(pcd.refact, 1, 0, 1),
- fco.name,
- pco.name
- from sys.obj$ fb,
- sys.cdef$ fcd,
- sys.ccol$ fcc,
- sys.col$ fcl,
- sys.con$ fco,
- sys.user$ fo,
- sys.obj$ pb,
- sys.cdef$ pcd,
- sys.ccol$ pcc,
- sys.col$ pcl,
- sys.con$ pco,
- sys.user$ po
- where fo.user# = fb.owner#
- and fb.type# = 2
- and fcd.enabled = 1
- and fcd.obj# = fb.obj#
- and fcc.con# = fcd.con#
- and fco.con# = fcd.con#
- and fcd.type# = 4
- and fcl.obj# = fcc.obj#
- and fcl.col# = fcc.col#
- and fcd.rcon# = pcd.con#
- and po.user# = pb.owner#
- and pb.type# = 2
- and pcd.obj# = pb.obj#
- and pcc.con# = pcd.con#
- and pco.con# = pcd.con#
- and pcl.obj# = pcc.obj#
- and pcl.col# = pcc.col#
- and pcc.pos# = fcc.pos#
- and (fb.owner# = uid
- or fb.obj# in (select obj#
- from sys.objauth$
- where grantee# in (select kzsrorol from x$kzsro))
- )
- and (pb.owner# = uid
- or pb.obj# in (select obj#
- from sys.objauth$
- where grantee# in (select kzsrorol from x$kzsro))
- )
- /
- comment on table OPENLINK$SQL_FOREIGN_KEYS is
- 'SQLForeignKeys for the OpenLink ODBC database agent'
- /
- grant select on OPENLINK$SQL_FOREIGN_KEYS to public with grant option
- /
- drop public synonym OPENLINK$SQL_FOREIGN_KEYS
- /
- create public synonym OPENLINK$SQL_FOREIGN_KEYS for OPENLINK$SQL_FOREIGN_KEYS
- /
- commit
- /
- create or replace view OPENLINK$SQL_PRIMARY_KEYS
- (TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, POSITION)
- as
- select NULL, po.name, pb.name, pcl.name, pcc.pos#
- from sys.obj$ pb,
- sys.cdef$ pcd,
- sys.ccol$ pcc,
- sys.col$ pcl,
- sys.user$ po
- where po.user# = pb.owner#
- and pb.type# = 2
- and pcd.type# = 2
- and pcd.obj# = pb.obj#
- and pcc.con# = pcd.con#
- and pcl.obj# = pcc.obj#
- and pcl.col# = pcc.col#
- and (pb.owner# = uid
- or pb.obj# in (select obj#
- from sys.objauth$
- where grantee# in (select kzsrorol from x$kzsro))
- )
- /
- comment on table OPENLINK$SQL_PRIMARY_KEYS is
- 'SQLPrimaryKeys for the OpenLink ODBC database agent'
- /
- grant select on OPENLINK$SQL_PRIMARY_KEYS to public with grant option
- /
- drop public synonym OPENLINK$SQL_PRIMARY_KEYS
- /
- create public synonym OPENLINK$SQL_PRIMARY_KEYS for OPENLINK$SQL_PRIMARY_KEYS
- /
- commit
- /
- create or replace view OPENLINK$SQL_SPEC_COLUMNS
- (SCOPE, COLUMN_NAME, DATA_TYPE, TYPE_NAME, PRECISION, LENGTH, SCALE,
- INT_ID, INT_COLS, INT_POS, TABLE_OWNER, TABLE_NAME, INDEX_NAME)
- as
- select 2, c.name, c.type#,
- decode(c.type#,
- 1, 'VARCHAR2',
- 2, decode (c.scale, null,
- decode(c.precision#, null, 'NUMBER', 'FLOAT'),
- 'NUMBER'),
- 8, 'LONG',
- 9, 'VARCHAR',
- 12,'DATE',
- 23,'RAW',
- 24,'LONG RAW',
- 69,'ROWID',
- 96,'CHAR',
- 105, 'MLSLABEL',
- 106, 'MLSLABEL',
- 'UNDEFINED'
- ),
- c.precision#, c.length, c.scale, 1, i.cols, ic.pos#, towner.name, t.name,
- i_o.name
- from
- sys.obj$ t,
- sys.col$ c,
- sys.ind$ i,
- sys.icol$ ic,
- sys.obj$ i_o,
- sys.user$ towner
- where t.type# = 2
- and towner.user# = t.owner#
- and i.bo# = t.obj#
- and i.property = 1
- and i_o.obj# = i.obj#
- and ic.obj# = i_o.obj#
- and ic.col# = c.col#
- and c.obj# = t.obj#
- and (t.owner# = uid
- or t.obj# in (select obj#
- from sys.objauth$
- where grantee# in (select kzsrorol from x$kzsro))
- )
- union
- select 1, 'ROWID', 1, 'CHAR', 18, 18, 0, 2, 1, 1, towner.name,
- t.name, ''
- from
- sys.obj$ t,
- sys.user$ towner
- where t.type# = 2
- and towner.user# = t.owner#
- and (t.owner# = uid
- or t.obj# in (select obj#
- from sys.objauth$
- where grantee# in (select kzsrorol from x$kzsro))
- )
- /
- comment on table OPENLINK$SQL_SPEC_COLUMNS is
- 'SQLSpecialColumns for the OpenLink ODBC database agent'
- /
- grant select on OPENLINK$SQL_SPEC_COLUMNS to public with grant option
- /
- drop public synonym OPENLINK$SQL_SPEC_COLUMNS
- /
- create public synonym OPENLINK$SQL_SPEC_COLUMNS for OPENLINK$SQL_SPEC_COLUMNS
- /
-
- Rem o.type# - 9 is packaged objects, 7 is a stored function, 8 is a stored procedure
-
- create or replace view OPENLINK$SQL_PROC_COLUMNS
- (PROCEDURE_QUALIFIER,PROCEDURE_OWNER,PROCEDURE_NAME,COLUMN_NAME,
- COLUMN_TYPE,DATA_TYPE,TYPE_NAME,PRECISION,LENGTH,SCALE,RADIX,
- NULLABLE,REMARKS)
- as
- select NULL,
- u.name,
- o.name,
- decode(a.position#,
- 0,'RETURN_VALUE',
- a.argument),
- decode(a.in_out,
- NULL,1,
- 2,2,
- 1,decode(a.position#,
- 0,5,
- 4),
- 1),
- a.type#,
- decode(a.type#,
- 1, 'VARCHAR2',
- 2, decode (a.scale,
- null, decode(a.precision#,
- null, 'NUMBER',
- 'FLOAT'),
- 'NUMBER'),
- 8, 'LONG',
- 9, 'VARCHAR',
- 12,'DATE',
- 23,'RAW',
- 24,'LONG RAW',
- 69,'ROWID',
- 96,'CHAR',
- 105, 'MLSLABEL',
- 106, 'MLSLABEL',
- 'UNDEFINED'
- ),
- decode(a.precision#,
- null,decode(a.type#,
- 1,255,
- 255),
- a.precision#),
- a.length,
- a.scale,
- a.radix,
- 2,
- NULL
- from
- sys.obj$ o,
- sys.argument$ a,
- sys.user$ u
- where
- u.user# = o.owner# and
- a.obj#=o.obj# and
- o.type# in (7,8) and
- o.status=1
- UNION
- select NULL,
- o.name,
- a.procedure$,
- decode(a.position#,
- 0,'RETURN_VALUE',
- a.argument),
- decode(a.in_out,
- NULL,1,
- 2,2,
- 1,decode(a.position#,
- 0,5,
- 4),
- 1),
- a.type#,
- decode(a.type#,
- 1, 'VARCHAR2',
- 2, decode (a.scale,
- null, decode(a.precision#,
- null, 'NUMBER',
- 'FLOAT'),
- 'NUMBER'),
- 8, 'LONG',
- 9, 'VARCHAR',
- 12,'DATE',
- 23,'RAW',
- 24,'LONG RAW',
- 69,'ROWID',
- 96,'CHAR',
- 105, 'MLSLABEL',
- 106, 'MLSLABEL',
- 'UNDEFINED'
- ),
- decode(a.precision#,
- null,decode(a.type#,
- 1,255,
- 255),
- a.precision#),
- a.length,
- a.scale,
- a.radix,
- 2,
- NULL
- from
- sys.obj$ o,
- sys.argument$ a,
- sys.user$ u
- where
- u.user# = o.owner# and
- a.obj#=o.obj# and
- o.type# = 9 and
- o.status=1
- /
- comment on table OPENLINK$SQL_PROC_COLUMNS is
- 'SQLProcedureColumns for the OpenLink ODBC database agent'
- /
- grant select on OPENLINK$SQL_PROC_COLUMNS to public with grant option
- /
- drop public synonym OPENLINK$SQL_PROC_COLUMNS
- /
- create public synonym OPENLINK$SQL_PROC_COLUMNS for OPENLINK$SQL_PROC_COLUMNS
- /
- commit
- /
-
- Rem
- Rem STORED FUNCTIONS FOR SCALAR FUNCTION SUPPORT
- Rem
- drop package OPENLINK;
- /
-
- create package OPENLINK as
- function INSERT_STRING(stra in string, strt in number, leng in number, strb in string) return string;
- pragma restrict_references(INSERT_STRING,wnds,wnps);
- function LEFT(stra in string, cnt in number) return string;
- pragma restrict_references(LEFT,wnds,wnps);
- function RIGHT(stra in string, cnt in number) return string;
- pragma restrict_references(RIGHT,wnds,wnps);
- function LOCATE(stra in string, strb in string, strt in number default 1) return number;
- pragma restrict_references(LOCATE,wnds,wnps);
- function REPEAT(strin in string, cnt in number) return string;
- pragma restrict_references(REPEAT,wnds,wnps);
- function RADIANS(degrees in float) return float;
- pragma restrict_references(RADIANS,wnds,wnps);
- function DEGREES(radians in float) return float;
- pragma restrict_references(DEGREES,wnds,wnps);
- function COT(radians in float) return float;
- pragma restrict_references(COT,wnds,wnps);
- function COUNT_IN_PARMS(obj_id in number) return number;
- pragma restrict_references(COUNT_IN_PARMS,wnds,wnps);
- function COUNT_OUT_PARMS(obj_id in number) return number;
- pragma restrict_references(COUNT_OUT_PARMS,wnds,wnps);
- function PCOUNT_IN_PARMS(obj_id in number, proc in string) return number;
- pragma restrict_references(PCOUNT_IN_PARMS,wnds,wnps);
- function PCOUNT_OUT_PARMS(obj_id in number, proc in string) return number;
- pragma restrict_references(PCOUNT_OUT_PARMS,wnds,wnps);
- function PACK_OBJ_TYPE(obj_id in number, proc in string) return number;
- pragma restrict_references(PACK_OBJ_TYPE,wnds,wnps);
- end;
- /
-
- create package body OPENLINK as
-
- function INSERT_STRING(stra in string, strt in number, leng in number, strb in string)
- return string as
- begin
- return SUBSTR(stra,1,strt-1) || strb || SUBSTR(stra,strt+leng);
- end;
-
- function LEFT(stra in string, cnt in number)
- return string as
- begin
- return SUBSTR(stra,1,cnt);
- end;
-
- function RIGHT(stra in string, cnt in number)
- return string as
- begin
- return SUBSTR(stra,(LENGTH(stra)-cnt)+1);
- end;
-
- function LOCATE(stra in string, strb in string, strt in number default 1)
- return number as
- begin
- return INSTR(strb,stra,strt);
- end;
-
- function REPEAT(strin in string, cnt in number)
- return string as
- begin
- declare
- tally number;
- outstring string(2000);
- begin
- tally := cnt;
- WHILE tally!=0 LOOP
- outstring := outstring || strin;
- tally := tally-1;
- END LOOP;
- return outstring;
- end;
- end;
-
- function RADIANS(degrees in float)
- return float as
- begin
- return (degrees/360)*2*3.1415926545;
- end;
-
- function DEGREES(radians in float)
- return float as
- begin
- return (radians/(2*3.141592654))*360;
- end;
-
- function COT(radians in float)
- return float as
- begin
- return (1/tan(radians));
- end;
-
- function COUNT_IN_PARMS(obj_id in number)
- return number as
- nparms NUMBER;
- begin
- select count(*) into nparms
- from argument$ a
- where a.obj#=obj_id
- and in_out is null;
- if nparms is null then
- nparms:=0;
- end if;
- return nparms;
- end;
-
- function COUNT_OUT_PARMS(obj_id in number)
- return number as
- nparms NUMBER;
- begin
- select count(*) into nparms
- from argument$ a
- where a.obj#=obj_id
- and in_out=1;
- if nparms is null then
- nparms:=0;
- end if;
- return nparms;
- end;
-
- function PCOUNT_IN_PARMS(obj_id in number, proc in string)
- return number as
- nparms NUMBER;
- begin
- select count(*) into nparms
- from argument$ a, obj$ o
- where a.obj#=obj_id
- and a.obj#=o.obj#
- and o.type#=9
- and a.procedure$=proc
- and in_out is null;
- if nparms is null then
- nparms:=0;
- end if;
- return nparms;
- end;
-
- function PCOUNT_OUT_PARMS(obj_id in number, proc in string)
- return number as
- nparms NUMBER;
- begin
- select count(*) into nparms
- from argument$ a, obj$ o
- where a.obj#=obj_id
- and a.obj#=o.obj#
- and o.type#=9
- and a.procedure$=proc
- and in_out=1;
- if nparms is null then
- nparms:=0;
- end if;
- return nparms;
- end;
-
- function PACK_OBJ_TYPE(obj_id in number, proc in string)
- return number as
- objtype# NUMBER;
- begin
- begin
- select a.position# into objtype#
- from argument$ a, obj$ o
- where a.obj#=obj_id
- and a.procedure$=proc
- and o.obj#=a.obj#
- and o.type#=9
- and a.position#=0
- and a.in_out=1;
- exception
- when too_many_rows then
- objtype#:=1;
- end;
- if objtype#=0 then
- objtype#:=2;
- else
- objtype#:=1;
- end if;
- return objtype#;
- end;
-
- end openlink;
- /
- show errors;
- /
- Rem
- Rem NOTE: We use the oplora7 name for now, to be compatible with the
- Rem oracle 7 agent. In future we may change the name.
- Rem
- drop public synonym OPLORA7;
- /
- create public synonym OPLORA7 for sys.openlink;
- /
- grant execute on sys.openlink to public with grant option;
- /
- alter package openlink COMPILE;
- /
-
- Rem
- Rem Note: Function type# for Packaged Objects not supported as yet.
- Rem Awaiting feedback from Oracle Corp on outstanding issue.
- Rem
- create or replace view OPENLINK$SQL_PROCEDURES
- (PROCEDURE_QUALIFIER,PROCEDURE_OWNER,PROCEDURE_NAME,NUM_INPUT_PARAMS,
- NUM_OUTPUT_PARAMS,NUM_RESULT_SETS,REMARKS,PROCEDURE_TYPE)
- as
- select NULL,
- u.name,
- o.name,
- openlink.count_in_parms(o.obj#),
- openlink.count_out_parms(o.obj#),
- -1,
- NULL,
- decode(o.type#,7,1,8,2,0)
- from
- sys.obj$ o,
- sys.user$ u
- where
- u.user#=o.owner# and
- o.type# in (7,8) and
- o.status=1
- union
- select NULL,
- o1.name,
- a1.procedure$,
- openlink.pcount_in_parms(o1.obj#,a1.procedure$),
- openlink.pcount_out_parms(o1.obj#,a1.procedure$),
- -1,
- NULL,
- 0
- from
- obj$ o1,
- argument$ a1
- where
- a1.obj#=o1.obj# and
- o1.type#=9 and
- o1.status=1
- /
- comment on table OPENLINK$SQL_PROCEDURES is
- 'SQLProcedures for the OpenLink ODBC database agent'
- /
- grant select on OPENLINK$SQL_PROCEDURES to public with grant option
- /
- drop public synonym OPENLINK$SQL_PROCEDURES
- /
- create public synonym OPENLINK$SQL_PROCEDURES for OPENLINK$SQL_PROCEDURES
- /
-
- commit
- /
-
-
- Rem
- Rem Note: Function type# for Packaged Objects not supported as yet.
- Rem Awaiting feedback from Oracle Corp on outstanding issue.
- Rem
- create or replace view OPENLINK$SQL_PROCS_NOCOUNT
- (PROCEDURE_QUALIFIER,PROCEDURE_OWNER,PROCEDURE_NAME,NUM_INPUT_PARAMS,
- NUM_OUTPUT_PARAMS,NUM_RESULT_SETS,REMARKS,PROCEDURE_TYPE)
- as
- select NULL,
- u.name,
- o.name,
- -1,
- -1,
- -1,
- NULL,
- decode(o.type#,7,1,8,2,0)
- from
- sys.obj$ o,
- sys.user$ u
- where
- u.user#=o.owner# and
- o.type# in (7,8) and
- o.status=1
- union
- select NULL,
- o1.name,
- a1.procedure$,
- -1,
- -1,
- -1,
- NULL,
- 0
- from
- obj$ o1,
- argument$ a1
- where
- a1.obj#=o1.obj# and
- o1.type#=9 and
- o1.status=1
- /
- comment on table OPENLINK$SQL_PROCS_NOCOUNT is
- 'SQLProcedures for the OpenLink ODBC database agent with no support for counting params'
- /
- grant select on OPENLINK$SQL_PROCS_NOCOUNT to public with grant option
- /
- drop public synonym OPENLINK$SQL_PROCS_NOCOUNT
- /
- create public synonym OPENLINK$SQL_PROCS_NOCOUNT for OPENLINK$SQL_PROCS_NOCOUNT
- /
-
- commit
- /
-
-
-